// QueryAnything: DataFactoryLT.cs
//
// Copyright 2007 Steve T. Pham
//
// This program is free software; you can redistribute it and/or
// modify it under the terms of the GNU General Public License
// as published by the Free Software Foundation; either version 2
// of the License, or (at your option) any later version.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with this program; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
//
// Linking this library statically or dynamically with other modules is
// making a combined work based on this library.  Thus, the terms and
// conditions of the GNU General Public License cover the whole
// combination.
// 
// As a special exception, the copyright holders of this library give you
// permission to link this library with independent modules to produce an
// executable, regardless of the license terms of these independent
// modules, and to copy and distribute the resulting executable under
// terms of your choice, provided that you also meet, for each linked
// independent module, the terms and conditions of the license of that
// module.  An independent module is a module which is not derived from
// or based on this library.  If you modify this library, you may extend
// this exception to your version of the library, but you are not
// obligated to do so.  If you do not wish to do so, delete this
// exception statement from your version.using System;

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Data;
using System.Text.RegularExpressions;

namespace SteveTPham.QueryAnything.DataAccess
{
	public class DataFactory
	{
		private string connectionString;
		private DbProviderFactory providerFactory;

		#region Constructors
		/// <summary>
		/// Create new object with given attributes
		/// </summary>
		/// <param name="connectionString"></param>
		public DataFactory(DatabaseProvider provider, string connectionString)
		{
			providerFactory = DbProviderFactories.GetFactory(DatabaseProviderTools.Convert(provider));

			TestConnection(connectionString);
			this.connectionString = connectionString;

		}
		#endregion

		#region Public methods
		/// <summary>
		/// Test connection string
		/// </summary>
		/// <param name="connectionString"></param>
		/// <returns></returns>
		public bool TestConnection(string connectionString)
		{
			try
			{
				using (DbConnection conn = GetConnection(connectionString))
				{
					conn.Open();
					conn.Close();
				}
				return true;
			}
			catch (Exception ex)
			{
				Debug.WriteLine(ex.Message);
				return false;
			}
		}

		/// <summary>
		/// Run Fill method
		/// </summary>
		/// <param name="query"></param>
		/// <returns></returns>
		public DataTable FillTable(string query)
		{
			using (DbConnection conn = GetConnection(this.connectionString))
			using (DbDataAdapter da = GetDataAdapter(query, conn))
			{
				DataTable dt = new DataTable();
				da.Fill(dt);
				return dt;
			}
		}
		#endregion

		#region ---- Provider Independent methods ----

		#region ---- GetConnection methods ----
		/// <summary>
		/// Create a database connection using the specified connection string.
		/// </summary>
		/// <param name="connectionString"></param>
		/// <returns></returns>
		public DbConnection GetConnection(string connectionString)
		{
			#region Sanity check
			if (String.IsNullOrEmpty(connectionString))
			{
				throw new ArgumentNullException("connectionString");
			}
			#endregion

			DbConnection conn = providerFactory.CreateConnection();

			conn.ConnectionString = connectionString;
			//conn.ConnectionTimeout = 90;

			return conn;
		}

		/// <summary>
		/// Create a database connection using the default connection string.
		/// </summary>
		/// <returns></returns>
		public DbConnection GetConnection()
		{
			return GetConnection(this.connectionString);
		}
		#endregion

		#region ---- GetCommand methods ----
		/// <summary>
		/// Create a database command using a specified query string,
		/// a DbConnection instance, and optionally an array of DbParameters.
		/// </summary>
		/// <param name="query"></param>
		/// <param name="conn"></param>
		/// <param name="parameters"></param>
		/// <returns></returns>
		public DbCommand GetCommand(string query, DbConnection conn, params DbParameter[] parameters)
		{
			#region Sanity check
			if (String.IsNullOrEmpty(query))
			{
				throw new ArgumentNullException("query");
			}

			if (conn == null)
			{
				throw new ArgumentNullException("conn");
			}
			#endregion

			DbCommand cmd = providerFactory.CreateCommand();

			cmd.CommandText = query;
			cmd.CommandType = CommandType.Text;
			cmd.CommandTimeout = 120;			// allow command to execute up to 120 sec.
			cmd.Connection = conn;

			cmd.Parameters.Clear();

			if (parameters.Length > 0)
			{
				if (Regex.IsMatch(query, @"^\s*(select|insert|update|delete)\s", RegexOptions.IgnoreCase))
					cmd.CommandType = CommandType.Text;
				else
					cmd.CommandType = CommandType.StoredProcedure;

				cmd.Parameters.AddRange(parameters);
			}

			return cmd;
		}

		/// <summary>
		/// Create a database command using a specified query string,
		/// a connection string, and optionally an array of DbParameters.
		/// </summary>
		/// <param name="query"></param>
		/// <param name="connectionString"></param>
		/// <param name="parameters"></param>
		/// <returns></returns>
		public DbCommand GetCommand(string query, string connectionString, params DbParameter[] parameters)
		{
			return GetCommand(query, GetConnection(connectionString), parameters);
		}

		public DbCommand GetCommand(string query, params DbParameter[] parameters)
		{
			return GetCommand(query, this.connectionString, parameters);
		}
		#endregion

		#region ---- GetDataAdapter methods ----
		/// <summary>
		/// Create a data adapter with the specified query string.
		/// </summary>
		/// <param name="query"></param>
		/// <returns></returns>
		public DbDataAdapter GetDataAdapter(string query)
		{
			return GetDataAdapter(query, GetConnection(this.connectionString));
		}

		/// <summary>
		/// Create a data adapter with the specified query string,
		/// a DbConnection instance, and an array of DbParameters.
		/// </summary>
		/// <param name="query"></param>
		/// <param name="conn"></param>
		/// <param name="parameters"></param>
		/// <returns></returns>
		public DbDataAdapter GetDataAdapter(string query, DbConnection conn, params DbParameter[] parameters)
		{
			DbDataAdapter da = providerFactory.CreateDataAdapter();
			da.SelectCommand = GetCommand(query, conn, parameters);

			return da;
		}

		public DbDataAdapter GetDataAdapter(DbCommand command)
		{
			DbDataAdapter da = providerFactory.CreateDataAdapter();
			da.SelectCommand = command;

			return da;
		}

		/// <summary>
		/// Create a data adapter with the specified query string, a
		/// DbConnection instance, and an array of DbParameters.
		/// </summary>
		/// <param name="query"></param>
		/// <param name="connectionString"></param>
		/// <param name="parameters"></param>
		/// <returns></returns>
		public DbDataAdapter GetDataAdapter(string query, string connectionString, params DbParameter[] parameters)
		{
			return GetDataAdapter(query, GetConnection(connectionString), parameters);
		}
		#endregion

		#region ---- GetParameter methods ----
		/// <summary>
		/// Create a database parameter using the specified parameters.
		/// </summary>
		/// <param name="name"></param>
		/// <param name="dbType"></param>
		/// <param name="size"></param>
		/// <param name="direction"></param>
		/// <param name="isNullable"></param>
		/// <param name="precision"></param>
		/// <param name="scale"></param>
		/// <param name="sourceColumn"></param>
		/// <param name="sourceVersion"></param>
		/// <param name="value"></param>
		/// <returns></returns>
		public DbParameter GetParameter(string name, DbType dbType, int size,
			ParameterDirection direction, bool isNullable, byte precision, byte scale,
			string sourceColumn, DataRowVersion sourceVersion, object value)
		{
			DbParameter p = providerFactory.CreateParameter();
			p.ParameterName = name;
			p.DbType = dbType;
			p.Size = size;
			p.Direction = direction;

			// TODO: where is isNullable, precision, and scale?
			//p.IsNullable = isNullable;
			//p.Precision = precision;
			//p.Scale = scale;

			p.SourceColumn = sourceColumn;
			p.SourceVersion = sourceVersion;
			p.Value = value;

			return p;
		}

		/// <summary>
		/// Create a database parameter using the specified parameters.
		/// </summary>
		/// <param name="name"></param>
		/// <param name="dbType"></param>
		/// <param name="size"></param>
		/// <param name="direction"></param>
		/// <param name="value"></param>
		/// <returns></returns>
		public DbParameter GetParameter(string name, DbType dbType, int size,
			ParameterDirection direction, object value)
		{
			return GetParameter(name, dbType, size, direction, false, 0, 0, string.Empty, DataRowVersion.Default, value);
		}

		/// <summary>
		/// create a parameter with Input direction type.
		/// </summary>
		/// <param name="name"></param>
		/// <param name="dbType"></param>
		/// <param name="size"></param>
		/// <param name="value"></param>
		/// <returns></returns>
		public DbParameter GetParameterIn(string name, DbType dbType, int size, object value)
		{
			return GetParameter(name, dbType, size, ParameterDirection.Input, false, 0, 0, string.Empty, DataRowVersion.Default, value);
		}

		/// <summary>
		/// create a parameter with Output direction type.
		/// </summary>
		/// <param name="name"></param>
		/// <param name="dbType"></param>
		/// <param name="size"></param>
		/// <param name="value"></param>
		/// <returns></returns>
		public DbParameter GetParameterOut(string name, DbType dbType, int size, object value)
		{
			return GetParameter(name, dbType, size, ParameterDirection.Output, false, 0, 0, string.Empty, DataRowVersion.Default, value);
		}

		/// <summary>
		/// create a parameter with InputOutput direction type.
		/// </summary>
		/// <param name="name"></param>
		/// <param name="dbType"></param>
		/// <param name="size"></param>
		/// <param name="value"></param>
		/// <returns></returns>
		public DbParameter GetParameterIO(string name, DbType dbType, int size, object value)
		{
			return GetParameter(name, dbType, size, ParameterDirection.InputOutput, false, 0, 0, string.Empty, DataRowVersion.Default, value);
		}

		/// <summary>
		/// create a parameter with ReturnValue direction type.
		/// </summary>
		/// <param name="name"></param>
		/// <param name="dbType"></param>
		/// <param name="size"></param>
		/// <param name="value"></param>
		/// <returns></returns>
		public DbParameter GetParameterRV(string name, DbType dbType, int size, object value)
		{
			return GetParameter(name, dbType, size, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, value);
		}
		#endregion

		#region ---- GetCommandBuilder methods ----
		/// <summary>
		/// Create a database command builder using the
		/// specified data adapter.
		/// </summary>
		/// <param name="da"></param>
		/// <returns></returns>
		public DbCommandBuilder GetCommandBuilder(DbDataAdapter da)
		{
			if (da == null)
				throw new ArgumentNullException("da");

			DbCommandBuilder commandBuilder = providerFactory.CreateCommandBuilder();

			commandBuilder.DataAdapter = da;

			return commandBuilder;
		}

		#endregion

		#region ---- GetTransaction methods ----
		//public DbTransaction BeginTransaction(DbConnection conn, IsolationLevel level)
		//{
		//}

		//public DbTransaction GetTransaction(DbConnection conn)
		//{
		//}
		#endregion

		#endregion
	}
}
